package ch.fhnw.kosmonauten.server.dal;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Locale;

import ch.fhnw.kosmonauten.model.Category;
import ch.fhnw.kosmonauten.model.User;

/**
 * Diese Klasse ruft die Benutzer-Informationen ab.
 * 
 * @author Fabian Siegrist
 * @author Oliver Freiermuth
 * @author Mikhail Onoufrienko
 * @author Risto Milani
 * @version $Revision: 202 $
 *          File name: $HeadURL: http://iching-kosmonauten.googlecode.com/svn/I-Ching/src/ch/fhnw/kosmonauten/server/dal/UserAccessLayer.java $
 *          Last modified:$Date: 2013-05-27 15:25:33 +0000 (Mon, 27 May 2013) $
 *          Last modified by: $Author: molotok.mihon@gmail.com $
 */

// class definition
@SuppressWarnings("serial")
public class UserAccessLayer implements Serializable{

	// attributes
	private Statement s;
	private ResultSet rs;
	private Connection con;

	// methods
	public String[] checkUser(String user, String password) throws SQLException {
		String[] usr = new String[5];
		try {
			con = DriverManager.getConnection(ConnectionString.url,
					ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			rs = s.executeQuery("select u.UserID, u.Name, u.Password, r.Rolename, sl.Languagename  from user u inner join systemLanguage sl on u.LanguageID = sl.LanguageID inner join role r on r.RoleID = u.RoleID where u.password='"
					+ password + "' and " + " u.name='" + user + "';");
			if (rs.next()) {
				usr[0] = rs.getString(1);
				usr[1] = rs.getString(2);
				usr[2] = rs.getString(3);
				usr[3] = rs.getString(4);
				usr[4] = rs.getString(5);
				s = con.createStatement();
				s.executeUpdate("insert into login (Logintime, UserID) "
						+ "values (" + "NOW()" + ", " + usr[0] + ")");
				return usr;
			} else {
				return new String[] { "" };
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		} finally {
			rs.close();
			s.close();
			con.close();
		}
	}
	
	public boolean saveUser(User user){
		
		String tempid = Integer.toString(user.getId());
		String tempname = user.getUsername();
		String temppass = user.getPassword();
		String temprole = "1";
		String templanguage = "1";
		if (user.getRole().equalsIgnoreCase("admin")){
			temprole = "2";
		}
		else if (user.getRole().equalsIgnoreCase("user")){
			temprole = "1";
		}
		
		if (user.getLanguage() == Locale.GERMAN){
			templanguage = "1";
		}
		else if (user.getLanguage() == Locale.ENGLISH){
			templanguage = "2";
		}
		try{
			con = DriverManager.getConnection(ConnectionString.url,ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			s.executeUpdate("UPDATE user SET Name = '" + tempname + "', Password = '" + temppass + "', RoleID = " + temprole + ", LanguageID = " + templanguage + " WHERE UserID = " + tempid + ";");
			System.out.println("SQL >>> UPDATE user SET Name = '" + tempname + "', Name = '" + tempname + "', Password = '" + temppass + "', RoleID = " + temprole + ", LanguageID = " + templanguage + " WHERE UserID = " + tempid + ";");
			s.close();
			con.close();
			return true;
		}
		catch (Exception e){
			System.out.println(e.toString());
			return false;
		}
	}
	
	public boolean createUser(User user){
		
		String tempname = user.getUsername();
		String temppass = user.getPassword();
		String temprole = "1";
		String templanguage = "1";
		if (user.getRole().equalsIgnoreCase("admin")){
			temprole = "2";
		}
		else if (user.getRole().equalsIgnoreCase("user")){
			temprole = "1";
		}
		
		if (user.getLanguage() == Locale.GERMAN){
			templanguage = "1";
		}
		else if (user.getLanguage() == Locale.ENGLISH){
			templanguage = "2";
		}
		try{
			con = DriverManager.getConnection(ConnectionString.url,ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			s.executeUpdate("INSERT INTO user (Name, Password, RoleID, LanguageID) values ('" + tempname + "', '" + temppass + "', " + temprole + ", " + templanguage + ");");
			System.out.println("SQL >>> CREATE user SET Name = '" + tempname + "', Name = '" + tempname + "', Password = '" + temppass + "', RoleID = " + temprole + ", LanguageID = " + templanguage + ";");
			s.close();
			con.close();
			return true;
		}
		catch (Exception e){
			System.out.println(e.toString());
			return false;
		}
	}
	
	public User[] getUsers() throws SQLException {

		ArrayList<User> response = new ArrayList<User>();

		try {
			con = DriverManager.getConnection(ConnectionString.url,
					ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			rs = s.executeQuery("select * from user;");
			while (rs.next()) {
				String temprole;
				String templanguage;
				if(rs.getString(4).equals("1")){
					temprole = "user";
				}
				else{
					temprole = "admin";
				}
				if(rs.getString(5).equals("1")){
					templanguage = "de";
				}
				else{
					templanguage = "en";
				}
				response.add(new User(Integer.parseInt(rs.getString(1)), rs.getString(2), rs.getString(3), temprole, templanguage));
			}
			return response.toArray(new User[0]);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}finally {
			rs.close();
			s.close();
			con.close();
		}
	}
	
	public boolean adminSaveUser(User user){
		
		String tempid = Integer.toString(user.getId());
		String tempname = user.getUsername();
		String temppass = user.getPassword();
		String temprole = "1";
		String templanguage = "1";
		if (user.getRole().equalsIgnoreCase("admin")){
			temprole = "2";
		}
		else if (user.getRole().equalsIgnoreCase("user")){
			temprole = "1";
		}
		
		if (user.getLanguage() == Locale.GERMAN){
			templanguage = "1";
		}
		else if (user.getLanguage() == Locale.ENGLISH){
			templanguage = "2";
		}
		try{
			con = DriverManager.getConnection(ConnectionString.url,ConnectionString.name, ConnectionString.passw);
			s = con.createStatement();
			s.executeUpdate("UPDATE user SET Name = '" + tempname + "', Password = '" + temppass + "', RoleID = " + temprole + ", LanguageID = " + templanguage + " WHERE UserID = " + tempid + ";");
			System.out.println("SQL >>> ADMIN UPDATE user = '" + tempname + "', Name = '" + tempname + "', Password = '" + temppass + "', RoleID = " + temprole + ", LanguageID = " + templanguage + " WHERE UserID = " + tempid + ";");
			s.close();
			con.close();
			return true;
		}
		catch (Exception e){
			System.out.println(e.toString());
			return false;
		}
	}
}
